package com.happy.qademo.websheet.controller;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.PostConstruct;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.sql.Connection;
import java.sql.Statement;
import java.time.LocalDateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.time.format.DateTimeFormatter;
import java.util.Random;

@CrossOrigin(origins = "*", allowedHeaders = "*")
@RestController
public class DatabaseOperation {
    private static final Logger logger = LoggerFactory.getLogger(DatabaseOperation.class);

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.url}")
    private String databaseurl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;
    

    @PostConstruct
    public void init() {
       
        try (Connection conn = DriverManager.getConnection(databaseurl, username, password);
             Statement stmt = conn.createStatement()) {

            // 创建模块表
            String createModuleTableSQL = "CREATE TABLE IF NOT EXISTS module (" + 
                                // "id BIGINT AUTO_INCREMENT PRIMARY KEY,"+
                                "moduleId VARCHAR(255) PRIMARY KEY,"+
                                "moduleUniKey VARCHAR(255),"+
                                "moduleName VARCHAR(255),"+
                                "version INT,"+
                                "status INT,"+
                                "projectUniKey VARCHAR(255)" + 
                                ");";
            stmt.execute(createModuleTableSQL);

            // 创建调试表
            String createDebugTableSQL = "CREATE TABLE IF NOT EXISTS debug_info (" + 
                                "id VARCHAR(255) PRIMARY KEY,"  +
                                "name VARCHAR(255)," +
                                "createTime VARCHAR(255)," +
                                "status INT," +
                                "sequence BIGINT," +
                                "interfaceId VARCHAR(255)," +
                                "moduleId VARCHAR(255)," +
                                "method VARCHAR(10)," +
                                "url VARCHAR(25500)," +
                                "params VARCHAR(20480)," +
                                "headers VARCHAR(20480)," +
                                "paramType VARCHAR(255)," +
                                "version INT," +
                                "uid VARCHAR(255)," +
                                "uniKey VARCHAR(255)," +
                                "moduleUniKey VARCHAR(255)," +
                                "projectUniKey VARCHAR(255)," +
                                "webModuleId VARCHAR(255)," +
                                "webProjectId VARCHAR(255)," +
                                "webId VARCHAR(255)" + 
                                ");";
            stmt.execute(createDebugTableSQL);

            System.out.println("数据库加载成功,sql load successfully.");

        } catch (SQLException e) {
            System.out.println("数据库连接或创建表时出现错误：" + e.getMessage());
        }

            
    }


    public static String generateUniqueId() {
        long timestamp = System.currentTimeMillis();
        UUID uuid = UUID.randomUUID();
        return String.valueOf(timestamp) + String.valueOf(uuid.hashCode()).substring(0, 8);
    }

    public static String generateIfEmpty(String value) {
        return value == null || value.isEmpty() ? generateUniqueId() : value;
    }
    
    public static String generateModuleId(String moduleId) {
        if (moduleId == null || moduleId.isEmpty()) {
            long timestamp = System.currentTimeMillis();  // 获取当前时间戳（毫秒）
            String randomStr = generateRandomString(8);  // 生成随机字符串
            moduleId = String.format("def-%d-%s", timestamp, randomStr);
        }
        return String.format(moduleId);
    }

    private static String generateRandomString(int length) {
        String characters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        Random random = new Random();
        StringBuilder sb = new StringBuilder(length);
        for (int i = 0; i < length; i++) {
            int index = random.nextInt(characters.length());
            sb.append(characters.charAt(index));
        }
        return sb.toString();
    }

    @PostMapping("/user/crapDebug/v1/synch.do")
    public ResponseEntity<Map<String, Object>> synchData(@RequestParam String projectUniKey,
            @RequestParam int plugVersion,
            @RequestParam String defProjectName,
            @RequestBody List<Map<String, Object>> requestData) {


        String projectName = defProjectName == null || defProjectName.isEmpty()? "默认调试项目" : defProjectName;
        projectUniKey = generateIfEmpty(projectUniKey);

        try (Connection conn = DriverManager.getConnection(databaseurl, username, password)) {
            // 插入模块数据
            for (Map<String, Object> item : requestData) {
                

                String moduleUniKey = (String) item.get("moduleId");
                String moduleName = (String) item.get("moduleName");
                String moduleId = (String) item.get("moduleId");
                int version =(int) item.get("version");
                // int status = (int) item.get("status");
                int status = 1;
                moduleId = generateModuleId(moduleId);
                // 1. 查询 moduleId 是否存在
                PreparedStatement checkStmt = conn.prepareStatement(
                    "SELECT COUNT(*) FROM module WHERE moduleId = ?");
                checkStmt.setString(1, moduleId);
                ResultSet rs = checkStmt.executeQuery();
                if (rs.next() && rs.getInt(1) > 0) {
                    // 2. 如果存在，执行更新操作
                    PreparedStatement updateStmt = conn.prepareStatement(
                        "UPDATE module SET moduleUniKey = ?, moduleName = ?, version = ?, status = ? ,projectUniKey  = ?  WHERE moduleId = ?");
                    updateStmt.setString(1, moduleUniKey);
                    updateStmt.setString(2, moduleName);
                    updateStmt.setInt(3, version);
                    updateStmt.setInt(4, status);
                    updateStmt.setString(5, projectUniKey);
                    updateStmt.setString(6, moduleId);
                    int rowsAffected = updateStmt.executeUpdate();
                    System.out.println("Update successful. Rows affected: " + rowsAffected);
                } else {
                PreparedStatement moduleStmt = conn.prepareStatement(
                        "INSERT INTO module (moduleUniKey, moduleName, moduleId, version, status, projectUniKey) VALUES (?,?,?,?,?,?) ");
                moduleStmt.setString(1, moduleUniKey);
                moduleStmt.setString(2, moduleName);
                moduleStmt.setString(3, moduleId);
                moduleStmt.setInt(4, version);
                moduleStmt.setInt(5, status);
                moduleStmt.setString(6, projectUniKey);
                moduleStmt.executeUpdate();}
            }

            // 插入调试数据
            for (Map<String, Object> item : requestData) {
                List<Map<String, Object>> debugs = (List<Map<String, Object>>) item.get("debugs");
                for (Map<String, Object> debug : debugs) {
                    String id = (String) debug.get("id");
                    id = generateIfEmpty(id);
                    String name = (String) debug.get("name");
                    String createTime = debug.get("createTime") == null? LocalDateTime.now()
                           .format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) : (String) debug
                                   .get("createTime");
                    // int status = (int) debug.get("status");
                    int status = 1;
                    long sequence = debug.get("sequence") == null ? (int) System.currentTimeMillis() : (int) debug.get("sequence");
                    String interfaceId = debug.get("interfaceId") == null? null : (String) debug.get("interfaceId");
                    String moduleId = (String) debug.get("moduleId");
                    String method = (String) debug.get("method");
                    String url = (String) debug.get("url");
                    String params = (String) debug.get("params");
                    String headers = (String) debug.get("headers");
                    String paramType = (String) debug.get("paramType");
                    int version = (int) debug.get("version");
                    String uid = "172715838751206023135";
                    // String uid = (String) debug.get("uid") == null? generateUniqueId() : (String) debug.get("uid");
                    String uniKey = (String) debug.get("id");
                    String moduleUniKey = (String) debug.get("moduleId");
                    String webModuleId = debug.get("webModuleId") == null? generateUniqueId() : (String) debug
                           .get("webModuleId");
                    String webProjectId = debug.get("webProjectId") == null? generateRandomString(20)+ "-debug"
                            : (String) debug.get("webProjectId");
                    String webId = debug.get("webId") == null? generateUniqueId() : (String) debug.get("webId");




                   // 1. 查询 id 是否存在
                    PreparedStatement debugcheckStmt = conn.prepareStatement(
                        "SELECT COUNT(*) FROM debug_info WHERE id = ?");
                        debugcheckStmt.setString(1, id);
                    ResultSet debugrs = debugcheckStmt.executeQuery();

                    if (debugrs.next() && debugrs.getInt(1) > 0) {
                    // 2. 如果存在，执行更新操作
                    PreparedStatement updateStmt = conn.prepareStatement(
                        "UPDATE debug_info SET name = ?, createTime = ?, status = ?, sequence = ?, interfaceId = ?, moduleId = ?, method = ?, url = ?, params = ?, headers = ?, paramType = ?, version = ?, uid = ?, uniKey = ?, moduleUniKey = ?, projectUniKey = ?, webModuleId = ?, webProjectId = ?, webId = ? WHERE id = ?");
                    
                    updateStmt.setString(1, name);
                    updateStmt.setString(2, createTime);
                    updateStmt.setInt(3, status);
                    updateStmt.setLong(4, sequence);
                    updateStmt.setString(5, interfaceId);
                    updateStmt.setString(6, moduleId);
                    updateStmt.setString(7, method);
                    updateStmt.setString(8, url);
                    updateStmt.setString(9, params);
                    updateStmt.setString(10, headers);
                    updateStmt.setString(11, paramType);
                    updateStmt.setInt(12, version);
                    updateStmt.setString(13, uid);
                    updateStmt.setString(14, uniKey);
                    updateStmt.setString(15, moduleUniKey);
                    updateStmt.setString(16, projectUniKey);
                    updateStmt.setString(17, webModuleId);
                    updateStmt.setString(18, webProjectId);
                    updateStmt.setString(19, webId);
                    updateStmt.setString(20, id); // 最后设置 id
                    
                    int rowsAffected = updateStmt.executeUpdate();
                    System.out.println("Update  debugTable successful. Rows affected: " + rowsAffected);
                } else {
                    // 3. 如果不存在，执行插入操作


                    PreparedStatement debugStmt = conn.prepareStatement(
                            "INSERT INTO debug_info (id, name, createTime, status, sequence, interfaceId, moduleId, method, url, params, headers, paramType, version, uid, uniKey, moduleUniKey, projectUniKey, webModuleId, webProjectId, webId) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                    debugStmt.setString(1, id);
                    debugStmt.setString(2, name);
                    debugStmt.setString(3, createTime);
                    debugStmt.setInt(4, status);
                    debugStmt.setLong(5, sequence);
                    debugStmt.setString(6, interfaceId);
                    debugStmt.setString(7, moduleId);
                    debugStmt.setString(8, method);
                    debugStmt.setString(9, url);
                    debugStmt.setString(10, params);
                    debugStmt.setString(11, headers);
                    debugStmt.setString(12, paramType);
                    debugStmt.setInt(13, version);
                    debugStmt.setString(14, uid);
                    debugStmt.setString(15, uniKey);
                    debugStmt.setString(16, moduleUniKey);
                    debugStmt.setString(17, projectUniKey);
                    debugStmt.setString(18, webModuleId);
                    debugStmt.setString(19, webProjectId);
                    debugStmt.setString(20, webId);
                    debugStmt.executeUpdate();
                    System.out.println("Insert debugTable successful.");
                }
                }
            }

            Map<String, Object> resultData = queryDataFromDatabase(databaseurl, username, password, projectName,
                    projectUniKey);
            return new ResponseEntity<>(resultData, HttpStatus.OK);

        } catch (SQLException e) {
            logger.error("数据库插入数据时出现错误：", e);
            Map<String, Object> resultData = queryDataFromDatabase(databaseurl, username, password, projectName,
                    projectUniKey);
            return new ResponseEntity<>(resultData, HttpStatus.OK);
        } catch (Exception e) {
            logger.error("出现未知错误：", e);
            return new ResponseEntity<>(getErrorResponse(), HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

    public static Map<String, Object> queryDataFromDatabase(String dataurl, String datausername, String datapassword,
            String projectName, String projectUniKey) {
        Map<String, Object> result = new HashMap<>();
        try (Connection conn = DriverManager.getConnection(dataurl, datausername, datapassword)) {

            Map<String, Object> projectData = new HashMap<>();
            projectData.put("projectUniKey", projectUniKey);
            projectData.put("projectName", projectName);
            projectData.put("projectCover", "logo.png");

            PreparedStatement moduleStmt = conn.prepareStatement("SELECT * FROM module");
            ResultSet moduleRs = moduleStmt.executeQuery();
            List<Map<String, Object>> modules = new ArrayList<>();
            while (moduleRs.next()) {
                Map<String, Object> moduleData = new HashMap<>();
                moduleData.put("moduleUniKey", moduleRs.getString("moduleUniKey"));
                moduleData.put("moduleName", moduleRs.getString("moduleName"));
                moduleData.put("moduleId", moduleRs.getString("moduleId"));
                moduleData.put("version", moduleRs.getInt("version"));
                moduleData.put("status", moduleRs.getInt("status"));

                // 查询调试数据
                String moduleUniKey = moduleRs.getString("moduleUniKey");
                PreparedStatement debugStmt = conn.prepareStatement(
                        "SELECT id, name, createTime, status, sequence, interfaceId, method, url, params, headers, paramType, version, uid, uniKey, moduleUniKey, projectUniKey, webModuleId, webProjectId, webId FROM debug_info WHERE moduleUniKey =?");
                debugStmt.setString(1, moduleUniKey);
                ResultSet debugRs = debugStmt.executeQuery();
                List<Map<String, Object>> debugs = new ArrayList<>();
                while (debugRs.next()) {
                    Map<String, Object> debugData = new HashMap<>();
                    debugData.put("id", debugRs.getString("id"));
                    debugData.put("name", debugRs.getString("name"));
                    debugData.put("createTime", debugRs.getString("createTime"));
                    debugData.put("status", debugRs.getInt("status"));
                    debugData.put("sequence", debugRs.getInt("sequence"));
                    debugData.put("interfaceId", debugRs.getString("interfaceId"));
                    debugData.put("method", debugRs.getString("method"));
                    debugData.put("url", debugRs.getString("url"));
                    debugData.put("params", debugRs.getString("params"));
                    debugData.put("headers", debugRs.getString("headers"));
                    debugData.put("paramType", debugRs.getString("paramType"));
                    debugData.put("version", debugRs.getInt("version"));
                    debugData.put("uid", debugRs.getString("uid"));
                    debugData.put("uniKey", debugRs.getString("uniKey"));
                    debugData.put("moduleUniKey", debugRs.getString("moduleUniKey"));
                    debugData.put("projectUniKey", debugRs.getString("projectUniKey"));
                    debugData.put("webModuleId", debugRs.getString("webModuleId"));
                    debugData.put("webProjectId", debugRs.getString("webProjectId"));
                    debugData.put("webId", debugRs.getString("webId"));
                    debugs.add(debugData);
                }
                moduleData.put("debugs", debugs);
                modules.add(moduleData);
            }
            projectData.put("moduleList", modules);
            result.put("data", projectData);
            result.put("success", 1);
            result.put("page", null);
            result.put("error", null);
            result.put("tipMessage", null);
            result.put("others", null);
            return result;
        } catch (SQLException e) {
            logger.error("查询数据库时出现错误：", e);
            result.put("data", "[]");
            result.put("success", 0);
            result.put("page", null);
            result.put("error", e);
            result.put("tipMessage", null);
            result.put("others", null);
            return result;
        }
    }

    private static Map<String, Object> getErrorResponse() {
        Map<String, Object> errorResponse = new HashMap<>();
        errorResponse.put("data", "[]");
        errorResponse.put("success", 0);
        errorResponse.put("page", null);
        errorResponse.put("error", "请求错误");
        errorResponse.put("tipMessage", null);
        errorResponse.put("others", null);
        return errorResponse;
    }
    




    @RequestMapping(value = "/user/loginOut.do")
    public ResponseEntity<Map<String, Object>> loginOut() {    
        Map<String, Object> result = new HashMap<>();
        result.put("data", "[成功]");
        result.put("success", 1);
        result.put("page", null);
        result.put("error", null);
        result.put("tipMessage", null);
        result.put("others", null);
        return new ResponseEntity<>( result, HttpStatus.OK);
    }
    
    @RequestMapping(value = "/deleteInterface", method = {RequestMethod.GET, RequestMethod.POST})
    public ResponseEntity<String> deleteHttpInterface(@RequestParam("idToDelete")  String idToDelete) {
        try (Connection conn = DriverManager.getConnection(databaseurl, username, password);
             Statement stmt = conn.createStatement()) {
            String sql = String.format("DELETE FROM debug_info WHERE id = '%s'", idToDelete);
            stmt.executeUpdate(sql);
            System.out.println("数据删除成功");
           
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return ResponseEntity.status(500)
                .body("数据从数据库删除失败");
        }
        
        return ResponseEntity.status(200)
                .body("数据从数据库删除成功");

    } 
   
    @RequestMapping(value = "/deleteModule", method = {RequestMethod.GET, RequestMethod.POST})
    public ResponseEntity<String> deleteHttpmoduleIdToDelete(@RequestParam("moduleIdToDelete")  String moduleIdToDelete) {
        try (Connection conn = DriverManager.getConnection(databaseurl, username, password);
             Statement stmt = conn.createStatement()) {
            String sql = String.format("DELETE FROM debug_info WHERE moduleId  = '%s'", moduleIdToDelete);
            stmt.executeUpdate(sql);
            String sql2 = String.format("DELETE FROM module WHERE moduleId = '%s'", moduleIdToDelete);
            stmt.executeUpdate(sql2);
            System.out.println("数据删除成功");
           
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return ResponseEntity.status(500)
                .body("数据从数据库删除失败");
        }
        
        return ResponseEntity.status(200)
                .body("数据从数据库删除成功");

    } 



}